# coding: utf-8

# 生成虚拟工程商，用于采购项目的投标

import os
import time
import json
import random

import set_env_path     # 请勿删除

from sqlalchemy import create_engine
from sqlalchemy.sql import text

engine = create_engine(
    os.getenv('DATABASE_URL')
    or 'mysql+pymysql://root:@127.0.0.1/wph_www_prod?charset=utf8'
)
conn = engine.connect()

family_name = [
    '丁', '万', '严', '丰', '乔', '于', '云', '井', '付', '代', '任', '伊', '伍', '但',
    '何', '余', '侯', '俞', '信', '倪', '傅', '党', '兰', '关', '冀', '冯', '刁', '刘',
    '单', '南', '卜', '卢', '卿', '原', '古', '史', '叶', '司', '吉', '后', '向', '吕',
    '吴', '周', '和', '唐', '商', '国', '夏', '姚', '姜', '姬', '娄', '孔', '孙', '孟',
    '季', '宁', '安', '宋', '宗', '宰', '封', '尚', '尤', '尹', '屈', '屠', '岳', '崔',
    '左', '巩', '巴', '布', '帅', '师', '席', '常', '平', '庞', '康', '廉', '廖', '张',
    '彭', '徐', '惠', '慎', '成', '戴', '房', '支', '敬', '文', '方', '时', '昌', '明',
    '易', '晏', '普', '曲', '曹', '曾', '朱', '朴', '李', '杜', '束', '来', '杨', '杭',
    '林', '柏', '柳', '柴', '栗', '桂', '梁', '梅', '楚', '樊', '欧', '武', '段', '殷',
    '毋', '毕', '毛', '江', '汤', '汪', '沈', '沙', '法', '洪', '涂', '渠', '温', '游',
    '潘', '炕', '焦', '熊', '牛', '玉', '王', '甄', '甘', '田', '申', '白', '百', '瞿',
    '石', '祁', '祝', '禄', '秦', '程', '穆', '窦', '章', '童', '符', '简', '管', '索',
    '纪', '纵', '缑', '罗', '翟', '耿', '聂', '职', '肖', '胡', '臧', '舒', '艾', '芦',
    '芮', '苏', '苑', '苗', '范', '茹', '荆', '莫', '葛', '董', '蒋', '蔡', '蔺', '薛',
    '衡', '袁', '裴', '褚', '解', '詹', '许', '谢', '谭', '谷', '贠', '费', '贺', '贾',
    '赛', '赵', '路', '车', '辛', '运', '进', '连', '邓', '邝', '邢', '邬', '邱', '邵',
    '邹', '郎', '郏', '郑', '郝', '郭', '都', '金', '钟', '钱', '铁', '闪', '闫', '闵',
    '闻', '阎', '阚', '阮', '阴', '陆', '陈', '陶', '雷', '霍', '靳', '韦', '韩', '项',
    '顾', '颜', '马', '高', '魏', '鲁', '鲍', '麻', '黄', '齐', '龚',
]


def generate_mobile(size=10):
    seed = '0123456789'
    ret = [random.choice(seed) for i in range(size)]
    return '6' + ''.join(ret)


def insert_virt_engineer(engineer_number=1000):
    # PART 1
    # 查询地区表，取 城市的 ID 备用
    area_id_map = {}
    select_area_sql = text("""
        select id,name from area where type=2;
    """)
    select_result = conn.execute(select_area_sql)
    for i in select_result:
        area_id_map[i.id] = i.name
    area_id_keys = list(area_id_map.keys())

    # 查询地区表，取 省份的 ID 备用
    prov_id_map = {}
    select_prov_sql = text("""
        select id,name from area where type=1;
    """)
    select_result = conn.execute(select_prov_sql)
    for i in select_result:
        prov_id_map[str(i.id)[:2]] = i.name

    # print(json.dumps(area_id_map, indent=4, ensure_ascii=False))
    # print(json.dumps(prov_id_map, indent=4, ensure_ascii=False))

    # PART 2
    insert_user_sql = text("""
        insert into user (
            nickname, mobile,
            area_id, prov, city,
            type, level, gender,
            status, active, is_virt,
            created_at, updated_at
        ) values (
            :nickname, :mobile,
            :area_id, :prov, :city,
            :type, :level, :gender,
            :status, :active, :is_virt,
            :created_at, :updated_at
        );
    """)

    insert_pack_sql = text("""
        insert into pack (
            uid, area_id, call_point, sms_point,
            accum_call_point, accum_sms_point,
            end_date, created_at, updated_at
        ) values (
            :uid, :area_id, :call_point, :sms_point,
            :accum_call_point, :accum_sms_point,
            :end_date, :created_at, :updated_at
        );
    """)

    find_last_insert_user_id_sql = text("""
        select LAST_INSERT_ID();
    """)

    use_mobile_set = set()
    for i in range(engineer_number):
        # 随机取一个姓，全部都是先生，性别男，随机取省，电话号码6开头
        area_id = random.choice(area_id_keys)

        mobile = generate_mobile()
        while mobile in use_mobile_set:
            mobile = generate_mobile()
        use_mobile_set.add(mobile)

        tmp_gender = random.randint(0, 5)
        if tmp_gender:
            gender = 1
            nickname = random.choice(family_name) + '先生'
        else:
            gender = 0
            nickname = random.choice(family_name) + '女士'

        tmp_user = dict(
            nickname=nickname,
            mobile=mobile,
            area_id=area_id,
            prov=prov_id_map.get(str(area_id)[:2], ''),
            city=area_id_map[area_id],
            type=100,
            level=10,
            gender=gender,
            status=0,
            active=1,
            is_virt=1,
            created_at=int(time.time()),
            updated_at=int(time.time()),
        )
        ins_result = conn.execute(
            insert_user_sql,
            **tmp_user
        )
        new_user_id = conn.execute(
            find_last_insert_user_id_sql
        ).fetchone()[0]

        # 写入套餐，关注该账户所在城市
        pack_area_id = json.dumps([tmp_user['area_id']])
        conn.execute(
            insert_pack_sql,
            uid=new_user_id,
            area_id=pack_area_id,
            call_point=0,
            sms_point=0,
            accum_call_point=0,
            accum_sms_point=0,
            end_date='2017-09-30',
            created_at=int(time.time()),
            updated_at=int(time.time()),
        )


if __name__ == '__main__':
    insert_virt_engineer()
